﻿Public Class clsProvincia

    Private id_prov As Integer
    Private NombreProvincia As String
    Private id_pais As Integer

    Public Property pid_pais() As Integer
        Get
            Return id_pais
        End Get
        Set(ByVal value As Integer)
            id_pais = value
        End Set
    End Property

    Public Property pNombreProvincia() As String
        Get
            Return NombreProvincia
        End Get
        Set(ByVal value As String)
            NombreProvincia = value
        End Set
    End Property
    Public Property pid_prov() As Integer
        Get
            Return id_prov
        End Get
        Set(ByVal value As Integer)
            id_prov = value
        End Set
    End Property

    'la siguiete propiedad es para los mensajes de error
    Private Msg As String
    Public Property pMsg() As String
        Get
            Return Msg
        End Get
        Set(ByVal value As String)
            Msg = value
        End Set
    End Property
    ' ver si usa
    Public Enum VerifExistType
        Ambos = 2
    End Enum

    Private dbConexion As clsConexion

    Public Sub New()
        dbConexion = New clsConexion
    End Sub
    Public Enum TipoQuery
        InfoTodo = 0
        InfoIndividual = 1
    End Enum

    Public dbDataRow As DataRow
    Public dbDataSet As DataSet


    Public Function fn_EjecutarQuery(ByVal Tipo As TipoQuery) As Boolean
        Try
            Dim EjecutarQuery As Boolean = False
            Dim SqlQuery As String
            If dbConexion.fn_abrir Then
                Select Case Tipo
                    Case TipoQuery.InfoIndividual
                        SqlQuery = " select * from Provincia " & _
                                   " where  id_prov = @id_prov and id_pais = @id_pais"

                        dbConexion.dbcommand = New Data.SqlClient.SqlCommand(SqlQuery, dbConexion.dbconnection)
                        dbConexion.dbcommand.Parameters.Add(New Data.SqlClient.SqlParameter("@id_pais", Data.SqlDbType.Int))
                        dbConexion.dbcommand.Parameters("@id_pais").Value = pid_pais

                        dbConexion.dbcommand.Parameters.Add(New Data.SqlClient.SqlParameter("@id_prov", Data.SqlDbType.Int))
                        dbConexion.dbcommand.Parameters("@id_prov").Value = pid_prov

                        dbConexion.dbdataadapter = New SqlClient.SqlDataAdapter(dbConexion.dbcommand)
                        dbConexion.dbdataset = New DataSet
                        dbConexion.dbdataadapter.Fill(dbConexion.dbdataset, "Provincia")

                        If dbConexion.dbdataset.Tables.Count > 0 Then
                            EjecutarQuery = True
                            dbDataRow = dbConexion.dbdataset.Tables("Provincia").Rows(0)
                            dbConexion.dbdataadapter.Dispose()
                            dbConexion.dbdataset.Dispose()
                            dbConexion.dbcommand.Dispose()
                            dbConexion.su_cerrar()
                        Else
                            Msg = "Error al obtener la información de Provincia."
                        End If
                    Case TipoQuery.InfoTodo
                        SqlQuery = " SELECT  * FROM ViewProvincias"
                        dbConexion.dbcommand = New Data.SqlClient.SqlCommand(SqlQuery, dbConexion.dbconnection)
                        dbConexion.dbdataadapter = New SqlClient.SqlDataAdapter(dbConexion.dbcommand)
                        dbConexion.dbdataadapter.Fill(dbConexion.dbdataset, "Provincia")
                        If dbConexion.dbdataset.Tables.Count > 0 Then
                            EjecutarQuery = True
                            dbDataSet = dbConexion.dbdataset.Copy()
                            dbConexion.dbdataadapter.Dispose()
                            dbConexion.dbdataset.Dispose()
                            dbConexion.dbcommand.Dispose()
                            dbConexion.su_cerrar()
                        Else
                            Msg = "Error al obtener la información de las Provincias."
                        End If
                End Select
            Else
                Msg = "No se pudo establecer la conexión con la bse de datos."
            End If
            Return (EjecutarQuery)
        Catch ex As Exception
            Msg = "Ocurrio un problema al ejecutar la consulta." & vbCrLf & ex.Message
            Return (False)
        End Try
    End Function

    Public Function fn_ValidarDatos(ByVal Operacion As String) As Boolean
        Select Case Operacion
            Case "Add", "Upd"
                If Trim(NombreProvincia) = "" Then
                    Msg = "Debe proporcionar el Nombre de la Provincia"
                    Return (False)
                End If
                If Trim(id_pais) = "" Or Trim(id_pais) = 0 Then
                    Msg = "Debe Seleccionar un Pais"
                    Return (False)
                End If
        End Select
        Return (True)
    End Function

    Public Function fn_Add() As Boolean
        Try
            Dim EjecucionOk As Boolean = False
            Dim SqlQuery As String

            If fn_ValidarDatos("Add") Then
                If dbConexion.fn_abrir Then
                    SqlQuery = " insert into Provincia(NombreProvincia,id_pais) " & _
                               " values(@NombreProvincia,@id_pais) "
                    dbConexion.dbcommand = New Data.SqlClient.SqlCommand(SqlQuery, dbConexion.dbconnection)
                    With dbConexion.dbcommand
                        .CommandType = CommandType.Text
                        .Parameters.Add(New Data.SqlClient.SqlParameter("@NombreProvincia", Data.SqlDbType.VarChar, 50))
                        .Parameters("@NombreProvincia").Value = pNombreProvincia

                        .Parameters.Add(New Data.SqlClient.SqlParameter("@id_pais", Data.SqlDbType.Int))
                        .Parameters("@id_pais").Value = pid_pais

                        If .ExecuteNonQuery() > 0 Then
                            EjecucionOk = True
                            dbConexion.dbcommand.Dispose()
                            dbConexion.su_cerrar()
                        Else
                            Msg = "Se obtuvo errores al agregar la Provincia."
                            EjecucionOk = False
                        End If
                    End With
                Else
                    EjecucionOk = False
                End If
            Else
                EjecucionOk = False
            End If
            Return (EjecucionOk)
        Catch ex As Exception
            Msg = "Error al agregar la Provincia." & vbCrLf & ex.Message
            If dbConexion.dbconnection.State = ConnectionState.Open Then dbConexion.su_cerrar()
            Return (False)
        End Try
    End Function
    Public Function fn_Del() As Boolean
        Try
            Dim EjecucionOk As Boolean = False
            Dim SqlQuery As String
            If fn_ValidarDatos("Del") Then
                If dbConexion.fn_abrir Then
                    SqlQuery = " delete from Provincia " & _
                               "where  id_prov = @id_prov and id_pais = @id_pais"
                    dbConexion.dbcommand = New Data.SqlClient.SqlCommand(SqlQuery, dbConexion.dbconnection)
                    With dbConexion.dbcommand
                        .CommandType = CommandType.Text
                        .Parameters.Add(New Data.SqlClient.SqlParameter("@id_pais", Data.SqlDbType.Int))
                        .Parameters("@id_pais").Value = pid_pais

                        .Parameters.Add(New Data.SqlClient.SqlParameter("@id_prov", Data.SqlDbType.Int))
                        .Parameters("@id_prov").Value = pid_prov

                        If .ExecuteNonQuery() > 0 Then
                            EjecucionOk = True
                            dbConexion.dbcommand.Dispose()
                            dbConexion.su_cerrar()
                        Else
                            Msg = "Se obtuvo errores al eliminar la Provincia"
                            EjecucionOk = False
                        End If
                    End With
                Else
                    EjecucionOk = False
                End If
            Else
                EjecucionOk = False
            End If
            Return (EjecucionOk)
        Catch ex As Exception
            Msg = "Error al eliminar la Provincia." & vbCrLf & ex.Message
            If dbConexion.dbconnection.State = ConnectionState.Open Then dbConexion.su_cerrar()
            Return (False)
        End Try
    End Function

    Public Function fn_Upd() As Boolean
        Try
            Dim EjecucionOk As Boolean = False
            Dim SqlQuery As String
            If fn_ValidarDatos("Upd") Then
                If dbConexion.fn_abrir Then
                    SqlQuery = " update  Provincia " & _
                               " set NombreProvincia = @NombreProvincia,id_pais = @id_pais" & _
                               " where   id_prov = @id_prov"
                    dbConexion.dbcommand = New Data.SqlClient.SqlCommand(SqlQuery, dbConexion.dbconnection)
                    With dbConexion.dbcommand
                        .CommandType = CommandType.Text
                        .Parameters.Add(New Data.SqlClient.SqlParameter("@NombreProvincia", Data.SqlDbType.VarChar, 50))
                        .Parameters("@NombreProvincia").Value = pNombreProvincia

                        .Parameters.Add(New Data.SqlClient.SqlParameter("@id_pais", Data.SqlDbType.Int))
                        .Parameters("@id_pais").Value = pid_pais

                        .Parameters.Add(New Data.SqlClient.SqlParameter("@id_prov", Data.SqlDbType.Int))
                        .Parameters("@id_prov").Value = pid_prov


                        If .ExecuteNonQuery() > 0 Then
                            EjecucionOk = True
                            dbConexion.dbcommand.Dispose()
                            dbConexion.su_cerrar()
                        Else
                            Msg = "Se obtuvo errores al modificar La Provincia."
                            EjecucionOk = False
                        End If
                    End With
                Else
                    EjecucionOk = False
                End If
            Else
                EjecucionOk = False
            End If
            Return (EjecucionOk)
        Catch ex As Exception
            Msg = "Error al agregar la Provincia." & vbCrLf & ex.Message
            If dbConexion.dbconnection.State = ConnectionState.Open Then dbConexion.su_cerrar()
            Return (False)
        End Try
    End Function

    Public Function fn_Existe(ByVal TipoVerificacion As VerifExistType) As Boolean
        Try
            Dim Existe As Boolean = False
            Dim SqlQuery As String
            If dbConexion.fn_abrir Then

                Select Case TipoVerificacion
                    Case VerifExistType.Ambos
                        SqlQuery = " select * from Provincia " & _
                                   " where   NombreProvincia = @NombreProvincia and id_pais = @id_pais"

                        dbConexion.dbcommand = New Data.SqlClient.SqlCommand(SqlQuery, dbConexion.dbconnection)

                        dbConexion.dbcommand.Parameters.Add(New Data.SqlClient.SqlParameter("@NombreProvincia", Data.SqlDbType.VarChar, 50))
                        dbConexion.dbcommand.Parameters("@NombreProvincia").Value = pNombreProvincia

                        dbConexion.dbcommand.Parameters.Add(New Data.SqlClient.SqlParameter("@id_pais ", Data.SqlDbType.Int))
                        dbConexion.dbcommand.Parameters("@id_pais").Value = pid_pais

                        dbConexion.dbcommand.CommandType = CommandType.Text
                        Msg = "la Provincia no Existe."
                End Select

                dbConexion.dbdataadapter = New SqlClient.SqlDataAdapter(dbConexion.dbcommand)
                dbConexion.dbdataset = New DataSet
                dbConexion.dbdataadapter.Fill(dbConexion.dbdataset, "Provincia")

                If dbConexion.dbdataset.Tables.Count > 0 Then
                    If dbConexion.dbdataset.Tables("Provincia").Rows.Count > 0 Then
                        Existe = True
                        Msg = "La Provincia Existe."
                    End If
                    dbDataSet = dbConexion.dbdataset.Copy()
                    dbConexion.dbdataadapter.Dispose()
                    dbConexion.dbdataset.Dispose()
                    dbConexion.dbcommand.Dispose()
                    dbConexion.su_cerrar()
                Else
                    Msg = "La Provincia no Existe."
                    Existe = False
                End If
            End If
            Return (Existe)
        Catch ex As Exception
            Msg = "Error al buscar si existe la Provincia." & vbCrLf & ex.Message
            Return (False)
        End Try
    End Function


End Class
